﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using HZ.Supervise.Model;
using System.Data.SqlClient;

namespace HZ.Supervise.DAL
{
    public class DAL_Company
    {
        public static DataTable GetCompany(string companyname, int currentpage, int pagesize, out int totalrecord)
        {
            //条件修改 by张雄
            //StringBuilder sb = new StringBuilder();
            //if (!string.IsNullOrEmpty(companyname))
            //{
            //    sb.AppendFormat("WHERE CompanyName LIKE '%{0}%'", companyname);
            //}
            string sql = string.Format("SELECT * FROM TB_Company where 1=1 {0} ORDER BY CompanyName", companyname);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        public static DataTable GetManageOrg(string manageOrgName, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();

            string sql = string.Format("SELECT * FROM TB_ManageOrg where ManageOrgName = '河北省药械采购中心' ORDER BY ManageOrgName");

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        public static DataTable GetCompanyName(string companyid, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder("1=1");
            if (!string.IsNullOrEmpty(companyid))
            {
                if (companyid != "所有企业")
                {
                    companyid = "'" + companyid.Replace(" ", "").Replace(",", "','") + "'";

                    sb.AppendFormat(" and CompanyID IN({0})", companyid);
                }
            }

            string sql = string.Format("select CompanyID,CompanyName from TB_Company where {0} ORDER BY CompanyName", sb);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }
        public static DataTable GetCompanyName(string companynameid, string companytype, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder("1=1");
            string sql = "";
            if (!string.IsNullOrEmpty(companynameid))
            {
                if (companynameid != "所有企业")
                {
                    sb.AppendFormat(" and (CompanyID IN('{0}') or CompanyName like '%{0}%')", companynameid);
                }
            }

            if (companytype == "0")
            {
                sb = sb.Replace("CompanyID", "CompanyID_SC").Replace("CompanyName", "CompanyName_SC");

                sql = string.Format("select distinct CompanyID_SC as CompanyID,CompanyName_SC as CompanyName from View_ProcureCatalog_TB where 1=1 and {0} ORDER BY CompanyName", sb);

            }
            else
            {

                sql = string.Format("select CompanyID,CompanyName from TB_Company where CompanyType={1} and {0} ORDER BY CompanyName", sb, companytype);

            }

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;

            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }
        /// <summary>
        /// 将企业加入到黑名单 0 失败 1成功 
        /// </summary>
        /// <param name="blackList">企业</param>
        /// <returns></returns>
        public static int InsertIntoBlackList(HZ.Supervise.Model.BlackList blackList)
        {
            int count = 0;
            try
            {

                count = HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.BlackList>(blackList);
            }
            catch
            {
                count = 0;
            }
            return count;
        }
        /// <summary>
        /// 将此投诉加入到投入表 0 失败 1成功 
        /// </summary>
        /// <param name="complain">企业</param>
        /// <returns>0 失败 1成功 </returns>
        public static int InsertIntoComplain(HZ.Supervise.Model.Complain complain)
        {
            int count = 0;
            try
            {

                count = HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.Complain>(complain);
            }
            catch
            {
                count = 0;
            }
            return count;
        }
        /// <summary>
        /// 获得企业详细信息
        /// </summary>
        /// <param name="companyname">企业名称</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetCompanyInfo(string companyname, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(companyname))
            {
                sb.AppendFormat("and CompanyName LIKE '%{0}%'", companyname);
            }
            string sql = string.Format("SELECT * FROM TB_CompanyInfo WHERE 1=1 {0} ORDER BY CompanyName", sb.ToString());

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 获得企业详细信息
        /// </summary>
        /// <param name="companyname">企业名称</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetCompanyInfo(Company com, int currentpage, int pagesize, out int totalrecord)
        {
            

            string where = DAL_Helper.GetWhereCondition<Company>(com, false);

            string sql = string.Format("SELECT * FROM TB_CompanyInfo WHERE  CompanyID LIKE 'J%' {0} ORDER BY CompanyName", where); 
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 获得不再黑名单中的所有企业
        /// </summary>
        /// <param name="companyname">企业名</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页最大数</param>
        /// <param name="totalrecord"></param>
        /// <returns>不在黑名单中的企业</returns>
        public static DataTable GetCompanyNotInBlackList(string companyname, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(companyname))
            {
                sb.AppendFormat("WHERE CompanyName LIKE '%{0}%'", companyname);
            }
            else
            {
                sb.AppendFormat("WHERE 1=1");
            }
            string sql = string.Format("SELECT * FROM TB_Company {0} and CompanyID not in(select CompanyID from TB_BlackList) order by CompanyName", sb.ToString());

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 获得黑名单中的企业
        /// </summary>
        /// <param name="companyname">企业名称</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns>黑名单中的企业</returns>
        public static DataTable GetBlackList(string companyname, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(companyname))
            {
                sb.AppendFormat("WHERE CompanyName LIKE '%{0}%'", companyname);
            }
            string sql = string.Format("SELECT * FROM TB_BlackList {0} ORDER BY CompanyName", sb.ToString());

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 获得投诉表中的投诉信息
        /// </summary>
        /// <param name="complain">投诉信息</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页最大数</param>
        /// <param name="totalrecord"></param>
        /// <returns>投诉信息</returns>
        public static DataTable GetComplain(HZ.Supervise.Model.Complain complain, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("where 1={0}", "1");
            if (!complain.ComplainTime.Equals("1900-2-2"))
            {
                sb.AppendFormat("and ComplainTime = '{0}'", complain.ComplainTime + " 00:00:00.000");
            }

            if (!string.IsNullOrEmpty(complain.ComplainantName))
            {
                sb.AppendFormat("and ComplainantName = '{0}'", complain.ComplainantName);
            }

            if (complain.Status != 3)
            {
                sb.AppendFormat("and Status = '{0}'", complain.Status);
            }

            if (!string.IsNullOrEmpty(complain.UserId))
            {

                sb.AppendFormat(" and UserId = '{0}'", complain.UserId);

            }
            if (!string.IsNullOrEmpty(complain.ComplainID))
            {
                sb.AppendFormat(" and ComplainID = '{0}'", complain.ComplainID);
            }

            string sql = string.Format("SELECT * FROM TB_Complain {0} ORDER BY ComplainTime", sb.ToString());

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 获得医院
        /// </summary>
        /// <param name="companyname"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetHospital(HZ.Supervise.Model.Hospital Hospital, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("where 1={0}", "1");
            if (!string.IsNullOrEmpty(Hospital.HospitalName))
            {
                sb.AppendFormat("and HospitalName LIKE '%{0}%'", Hospital.HospitalName);
            }
            if (!string.IsNullOrEmpty(Hospital.IsGovernment))
            {
                sb.AppendFormat(" AND IsGovernment ='{0}'", Hospital.IsGovernment);
            }
            if (!string.IsNullOrEmpty(Hospital.Levels ))
            {
                sb.AppendFormat(" AND Levels IN ({0})", Hospital.Levels);

            }
            if (!string.IsNullOrEmpty(Hospital.IsCountry))
            {

                sb.AppendFormat(" AND IsCountryDetail IN ({0})", Hospital.IsCountry);
            }
            if (!string.IsNullOrEmpty(Hospital.HospitalID))
            {

                sb.AppendFormat(" AND HospitalID ='{0}'", Hospital.HospitalID);
            }
             
            string sql = string.Format("SELECT * FROM TB_Hospital {0} ORDER BY HospitalId", sb.ToString());

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 回复投诉 0失败 1 成功
        /// </summary>
        /// <param name="Complain"></param>
        /// <returns></returns>
        public static int ReplyComplain(HZ.Supervise.Model.Complain Complain)
        {
            try
            {
                return HZ.Data.DAL_Helper.UpdateModel<Model.Complain>(Complain);
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        public static DataTable GeteCompany_PSRanking1(string orderOfRank, string orderBy, string wherePm, string wherePms, string whereCompanyPs, string sortSource, int currentpage, int pagesize, out int totalrecord)
        {


            string sql = "";

            //if (sortSource == "2")
            //{


            //    sql = string.Format("select pm,pms,A.CompanyName,A.CompanyID_PS,PurchaseAmountTotal,DistributeAmountTotal,2 as sortsource, WarehouseAmountTotal,WareHouseRate,DistributeRate from (( select RANK () OVER(order by   {0}  DESC ) as pm, CompanyName,CompanyID_PS,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when  CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT where 1=1  {2}  group by CompanyName,CompanyID_PS) A left join (select RANK () OVER(order by  {0} DESC ) as pms,CompanyID_PS, CompanyName from CACHE_STAT where 1=1 {3} group by CompanyID_PS,CompanyName ) B on A.CompanyName=B.CompanyName and A.CompanyID_PS=B.CompanyID_PS) where 1=1 {4} order by {1}", orderOfRank, orderBy, wherePm, wherePms, whereCompanyPs);

            //}
            //else
            //{

            sql = string.Format("select pm,pms,A.CompanyName,A.CompanyID_PS,PurchaseAmountTotal,DistributeAmountTotal,WarehouseAmountTotal,WareHouseRate,DistributeRate,A.SortSorce as SortSource from (( select RANK () OVER(order by {0} DESC ) as pm, CompanyName,CompanyID_PS,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SortSorce,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when  CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT where 1=1 {2}  group by CompanyName,CompanyID_PS,SortSorce) A left join (select RANK () OVER(order by {0}  DESC ) as pms,CompanyName,CompanyID_PS,SortSorce,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when  CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouse , case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as Distribute from CACHE_STAT where 1=1 {3}  group by CompanyName,CompanyID_PS,SortSorce ) B on A.CompanyName=B.CompanyName and A.CompanyID_PS=B.CompanyID_PS and A.SortSorce=B.SortSorce) where 1=1 {4} order by {1}", orderOfRank, orderBy, wherePm, wherePms, whereCompanyPs);

            //}

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;

            totalrecord = des.TotalCount;

            return des.GetSpecailPage();

        }

        /// <summary>
        /// 返回月排名
        /// </summary>
        /// <param name="_strmonth1">选择月份</param>
        /// <param name="_strmonth2">上月份</param>
        /// <param name="_strorderby">排序字段</param>
        ///<param name="strwhere">查询条件</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GeteCompany_PSRanking(string Sort, string time1, string time2, string where, string orderby, string dropmode, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = "";

            //2011-0427 ml
            if (dropmode.Equals("2"))//不分类别
            {
                if (Sort == "WareHouseRate")
                {
                    sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case when ISNULL(a.DistributeAmountTotal,0)=0 then 0 
when ISNULL(purchaseamount2daysagototal,0)=0 then 0  else CONVERT(FLOAT, A.DistributeAmountTotal/purchaseamount2daysagototal ) end  AS DistributeRate,Case when ISNULL(A.WarehouseAmountTotal,0)=0 then 0 when ISNULL(PurchaseAmount4DaysAgoTotal,0)=0 then 0
else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WarehouseRate,CompanyName ,99 as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY (SUM(DistributeAmount)/SUM(PurchaseAmount)) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount2daysagototal,SUM(PurchaseAmount) as  PurchaseAmount4DaysAgoTotal,CompanyID_PS,AreaID,DayTime,CompanyName FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY CompanyID_PS,CompanyName,AreaID,DayTime ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY (SUM(DistributeAmount)/SUM(PurchaseAmount)) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,CompanyID_PS,AreaID,DayTime FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY CompanyID_PS,AreaID,DayTime) AS B ON A.CompanyID_PS=B.CompanyID_PS  AND A.AreaID=B.AreaID    ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
                }
                else
                {
                    sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case when ISNULL(a.DistributeAmountTotal,0)=0 then 0 
when ISNULL(purchaseamount2daysagototal,0)=0 then 0  else CONVERT(FLOAT, A.DistributeAmountTotal/purchaseamount2daysagototal ) end  AS DistributeRate,Case when ISNULL(A.WarehouseAmountTotal,0)=0 then 0 when ISNULL(PurchaseAmount4DaysAgoTotal,0)=0 then 0
else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WarehouseRate,CompanyName ,99 as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY SUM({0}) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount2daysagototal,SUM(PurchaseAmount) as  PurchaseAmount4DaysAgoTotal,  CompanyID_PS,AreaID,DayTime,CompanyName FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY CompanyID_PS,CompanyName,AreaID,DayTime ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY SUM({0}) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,CompanyID_PS,AreaID,DayTime FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY CompanyID_PS,AreaID,DayTime) AS B ON A.CompanyID_PS=B.CompanyID_PS  AND A.AreaID=B.AreaID    ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
                }

            }
            else
            {
                if (Sort == "WareHouseRate")
                {
                    sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case when a.DistributeAmountTotal=0 then 0 when purchaseamount2daysagototal=0 then 0 else CONVERT(FLOAT, A.DistributeAmountTotal/purchaseamount2daysagototal ) end  AS DistributeRate,Case when ISNULL(A.WarehouseAmountTotal,0)=0 then 0 when ISNULL(PurchaseAmount4DaysAgoTotal,0)=0 then 0
else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WarehouseRate,CompanyName ,a.SortSorce  as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY (SUM(DistributeAmount)/SUM(PurchaseAmount)) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount2daysagototal, SUM(PurchaseAmount) as  PurchaseAmount4DaysAgoTotal,  CompanyID_PS,AreaID,DayTime,CompanyName,SortSorce FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY CompanyID_PS,CompanyName,AreaID,DayTime,SortSorce ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY (SUM(DistributeAmount)/SUM(PurchaseAmount)) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,CompanyID_PS,AreaID,DayTime,SortSorce FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY CompanyID_PS,AreaID,DayTime,SortSorce) AS B ON A.CompanyID_PS=B.CompanyID_PS  AND A.AreaID=B.AreaID  AND A.SortSorce=B.SortSorce  ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
                }
                else
                {
                    sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case when a.DistributeAmountTotal=0 then 0 when purchaseamount2daysagototal=0 then 0 else CONVERT(FLOAT, A.DistributeAmountTotal/purchaseamount2daysagototal ) end  AS DistributeRate,Case when ISNULL(A.WarehouseAmountTotal,0)=0 then 0 when ISNULL(PurchaseAmount4DaysAgoTotal,0)=0 then 0
else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WarehouseRate,CompanyName ,a.SortSorce as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY SUM({0}) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount2daysagototal,SUM(PurchaseAmount) as  PurchaseAmount4DaysAgoTotal,   CompanyID_PS,AreaID,DayTime,CompanyName,SortSorce FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY CompanyID_PS,CompanyName,AreaID,DayTime,SortSorce ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY SUM({0}) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,CompanyID_PS,AreaID,DayTime,SortSorce FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY CompanyID_PS,AreaID,DayTime,SortSorce) AS B ON A.CompanyID_PS=B.CompanyID_PS  AND A.AreaID=B.AreaID AND A.SortSorce=B.SortSorce   ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
                }

            }

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }
        /// <summary>
        /// 返回月排名
        /// </summary>
        /// <param name="_strmonth1">选择月份</param>
        /// <param name="_strmonth2">上月份</param>
        /// <param name="_strorderby">排序字段</param>
        ///<param name="strwhere">查询条件</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GeteCompany_PSRankingTime(string _strmonth1, string _strorderby, string strwhere1, string strsortsource, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = "";
            //=2不分类别
            //2011.04.18 张雄 modify
            if (strsortsource.Equals("2"))//不分类别
            {
                sql = "select a.pm apm,a.CompanyName_PS, 99 SortSource,a.PurchaseCountTotal,a.PurchaseAmountTotal,a.DistributeCountTotal,a.DistributeAmountTotal,a.DistributeRate from (SELECT RANK() over(order by " + _strorderby + " )pm ,b.CompanyName_PS, PurchaseCountTotal, PurchaseAmountTotal, DistributeRate, DistributeCountTotal,   DistributeAmountTotal FROM (SELECt  CompanyName_PS ,Case  WHEN SUM(DistributeCount)=0 THEN NULL WHEN SUM(PurchaseCount)=0 THEN NULL  ELSE  CONVERT(FLOAT,SUM(DistributeCount*Price)) / SUM(PurchaseAmount) END   AS DistributeRate,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(DistributeCount) AS DistributeCountTotal ,SUM(DistributeCount*Price) AS DistributeAmountTotal FROM View_Supervise WHERE 1=1 and (" + _strmonth1 + ") GROUP BY CompanyName_PS ) AS B  )a ORDER BY apm ASC";
            }
            else
            {
                sql = "select a.pm apm,a.CompanyName_PS,a.SortSource,a.PurchaseCountTotal,a.PurchaseAmountTotal,a.DistributeCountTotal,a.DistributeAmountTotal,a.DistributeRate from (SELECT RANK() over(order by " + _strorderby + " )pm ,b.CompanyName_PS, B.SortSource,PurchaseCountTotal, PurchaseAmountTotal, DistributeRate, DistributeCountTotal,   DistributeAmountTotal FROM (SELECt  CompanyName_PS, SortSource AS SortSource,Case  WHEN SUM(DistributeCount)=0 THEN NULL WHEN SUM(PurchaseCount)=0 THEN NULL  ELSE  CONVERT(FLOAT,SUM(DistributeCount*Price)) / SUM(PurchaseAmount) END   AS DistributeRate,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(DistributeCount) AS DistributeCountTotal ,SUM(DistributeCount*Price) AS DistributeAmountTotal FROM View_Supervise WHERE 1=1 and  SortSource=" + strsortsource + " and " + _strmonth1 + "  GROUP BY SortSource,CompanyName_PS) AS B  )a ORDER BY apm ASC";
            }


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }

        /// <summary>
        /// 配送企业汇总统计
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetCompanyTotalInfoPS(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string strAreadId = string.Empty;
            string comAreadId = string.Empty;
            //string areaid = vs.AreaID;
            //if (areaid.Length < 5)
            //{

            //    strAreadId = string.Format("AND AreaID LIKE '{0}%'", areaid);
            //    comAreadId = string.Format(" AND TB_PurchaseOrderDetail.AreaID LIKE '{0}%'", areaid);
            //}
            //else
            //{
            //    areaid = vs.AreaID;
            //    strAreadId = string.Format("AND AreaID in ({0})", areaid);
            //    comAreadId = string.Format(" AND TB_PurchaseOrderDetail.AreaID in ({0})", areaid);
            //}


            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();

            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}

            }
            //HospitalID IN (SELECT * FROM dbo.TB_Hospital WHERE IsCountryDetail='' AND Levels IN (''){3} 
            string hoslevel = string.Empty;
            if (!string.IsNullOrEmpty(vs.IsCountry))
            {

                hoslevel += string.Format(" AND IsCountryDetail IN({0})", vs.IsCountry);

            }
            if (!string.IsNullOrEmpty(vs.Levels))
            {

                hoslevel += string.Format(" AND Levels IN ({0})", vs.Levels);

            }
            /*
             <asp:ListItem Value="0">所有药品类型</asp:ListItem>
                                    <asp:ListItem Value="1">基本药物</asp:ListItem>
                                    <asp:ListItem Value="4">非基本药物</asp:ListItem>
             */
            string sortsource = "";
            if (vs.SortSource == 1)
            {
                sortsource = string.Format(" AND SortSource IN ({0})", "'1','2','3'");
            }
            else if (vs.SortSource == 4)
            {
                sortsource = string.Format("   AND SortSource IN ({0}) ", "'4'");
            }
            if (!string.IsNullOrEmpty(vs.IsEssential))
            {
                sortsource += string.Format("AND  IsEssential='{0}'", vs.IsEssential);
            }
            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                hoslevel += string.Format(" AND IsGovernment='{0}'", vs.IsGovernment);
            }
            string sortsource1 = string.Format(" AND ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB WHERE 1=1 {0})", sortsource);
            string sortsource2 = string.Format(" AND A.ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB WHERE 1=1  {0})", sortsource);

            string hos = string.Format(" AND HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {0}) {1}", hoslevel, sortsource1);
            string hos5 = string.Format(" AND A.HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {0}) {1}", hoslevel, sortsource2);

            string orderby = " ORDER BY PurchaseAmount DESC";
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;

            }
            string sql = string.Empty;
            if (vs.Flag == "0")
            {
                sql = string.Format(@"
                                                SELECT  Com.*,
                                                ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,
                                                ISNULL(Dis.DistributeAmount, 0) DistributeAmount,
                                                ISNULL(Warehouse.WarehouseAmount, 0) WarehouseAmount,
                                                ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount,
                                                ISNULL(Warehouse.WarehouseAmount, 0) - ISNULL(ReturnMer.ReturnAmount,
                                                                                              0) payAmount
                                        FROM    ( SELECT    CompanyID,
                                                            CompanyName
                                                  FROM      TB_Company
                                                  WHERE     CompanyType = 2
                                                ) Com
                                                LEFT JOIN ( SELECT  CompanyID_PS CompanyID,
                                                                    SUM(PRICE * PurchaseCount) PurchaseAmount
                                                            FROM    TB_PurchaseOrderDetail
                                                            WHERE   1 = 1
                                                                    AND OrderDetailState > 0
                                                            GROUP BY CompanyID_PS
                                                          ) Pur ON Com.CompanyID = Pur.CompanyID
                                                LEFT JOIN ( SELECT  CompanyID_PS,
                                                                    SUM(Price * A.DistributeCount) DistributeAmount
                                                            FROM    TB_PurchaseOrderDetail A
                                                                    INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                    INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                            WHERE   1 = 1
                                                            GROUP BY CompanyID_PS
                                                          ) Dis ON Com.CompanyID = Dis.CompanyID_PS
                                                LEFT JOIN ( SELECT  CompanyID_PS,
                                                                    SUM(Price * A.WarehouseCount) WarehouseAmount
                                                            FROM    TB_PurchaseOrderDetail A
                                                                    INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                    INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                            WHERE   1 = 1
                                                                    AND DistributeState > 0
                                                            GROUP BY CompanyID_PS
                                                          ) Warehouse ON Com.CompanyID = Warehouse.CompanyID_PS
                                                LEFT JOIN ( SELECT  CompanyID,
                                                                    SUM(RetrunPrice * ReturnCount) ReturnAmount
                                                            FROM    dbo.TB_ReturnMerchandise A
                                                                    INNER JOIN TB_ReturnMerchandiseDetail B ON A.ReturnSerialID = B.ReturnSerialID
                                                            GROUP BY CompanyID
                                                          ) ReturnMer ON Com.CompanyID = ReturnMer.CompanyID
                                        ORDER BY PurchaseAmount DESC
                                                ", endTime, startTime, vs.CompanyName_PS, hos + comAreadId, hos + strAreadId, hos5 + strAreadId, orderby, vs.AreaID);
            }
            else
            {
                sql = string.Format(@"SELECT  Com.*,
                                                            ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,
                                                            ISNULL(Dis.DistributeAmount, 0) DistributeAmount,
                                                            ISNULL(Warehouse.WarehouseAmount, 0) WarehouseAmount,
                                                            ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount,
                                                            ISNULL(Warehouse.WarehouseAmount, 0) - ISNULL(ReturnMer.ReturnAmount,
                                                                                                          0) payAmount
                                                    FROM    ( SELECT    CompanyID,
                                                                        CompanyName
                                                              FROM      TB_Company
                                                              WHERE     CompanyType = 2
                                                            ) Com
                                                            LEFT JOIN ( SELECT  CompanyID_PS CompanyID,
                                                                                SUM(PRICE * PurchaseCount) PurchaseAmount
                                                                        FROM    TB_PurchaseOrderDetail
                                                                        WHERE   1 = 1
                                                                                AND OrderDetailState > 0
                                                                        GROUP BY CompanyID_PS
                                                                      ) Pur ON Com.CompanyID = Pur.CompanyID
                                                            LEFT JOIN ( SELECT  CompanyID_PS,
                                                                                SUM(Price * A.DistributeCount) DistributeAmount
                                                                        FROM    TB_PurchaseOrderDetail A
                                                                                INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                                INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                        WHERE   1 = 1
                                                                        GROUP BY CompanyID_PS
                                                                      ) Dis ON Com.CompanyID = Dis.CompanyID_PS
                                                            LEFT JOIN ( SELECT  CompanyID_PS,
                                                                                SUM(Price * A.WarehouseCount) WarehouseAmount
                                                                        FROM    TB_PurchaseOrderDetail A
                                                                                INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                                INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                        WHERE   1 = 1
                                                                                AND DistributeState > 0
                                                                        GROUP BY CompanyID_PS
                                                                      ) Warehouse ON Com.CompanyID = Warehouse.CompanyID_PS
                                                            LEFT JOIN ( SELECT  CompanyID,
                                                                                SUM(RetrunPrice * ReturnCount) ReturnAmount
                                                                        FROM    dbo.TB_ReturnMerchandise A
                                                                                INNER JOIN TB_ReturnMerchandiseDetail B ON A.ReturnSerialID = B.ReturnSerialID
                                                                        GROUP BY CompanyID
                                                                      ) ReturnMer ON Com.CompanyID = ReturnMer.CompanyID
                                                    ORDER BY PurchaseAmount DESC
                                                     ", endTime, startTime, vs.CompanyName_PS, hos + comAreadId, hos + strAreadId, hos5 + strAreadId, vs.AreaID);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }



        /// <summary>
        /// 生产企业汇总统计
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetCompanyTotalInfoSC(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string strAreadId = string.Empty;
            strAreadId = vs.AreaID;
            //string areaid = vs.AreaID;


            //if (areaid.Length < 5)
            //{

            //    strAreadId = string.Format("AND AreaID LIKE '{0}%'", areaid);
            //}
            //else
            //{
            //    strAreadId = string.Format("AND AreaID in ({0})", areaid);
            //}



            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();

            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}

            }
            //HospitalID IN (SELECT * FROM dbo.TB_Hospital WHERE IsCountryDetail='' AND Levels IN (''){3} 
            string hoslevel = string.Empty;
            if (!string.IsNullOrEmpty(vs.IsCountry))
            {

                hoslevel += string.Format(" AND IsCountryDetail IN({0})", vs.IsCountry);

            }
            if (!string.IsNullOrEmpty(vs.Levels))
            {

                hoslevel += string.Format(" AND Levels IN ({0})", vs.Levels);

            }
            /*
             <asp:ListItem Value="0">所有药品类型</asp:ListItem>
                                    <asp:ListItem Value="1">基本药物</asp:ListItem>
                                    <asp:ListItem Value="4">非基本药物</asp:ListItem>
             */
            string sortsource = "";
            if (vs.SortSource == 1)
            {
                sortsource = string.Format(" AND  SortSource IN ({0})", "'1','2','3'");
            }
            else if (vs.SortSource == 4)
            {
                sortsource = string.Format(" AND SortSource IN ({0}) ", "'4'");
            }
            if (!string.IsNullOrEmpty(vs.IsEssential))
            {
                sortsource += string.Format(" AND  IsEssential='{0}'", vs.IsEssential);
            }
            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                hoslevel += string.Format(" AND IsGovernment='{0}'", vs.IsGovernment);
            }
            string sortsource1 = string.Format(" AND ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB WHERE 1=1 {0})", sortsource);
            string sortsource2 = string.Format(" AND A.ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB  WHERE 1=1 {0})", sortsource);

            string hos = string.Format(" AND HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {0}) {1}", hoslevel, sortsource1);
            string hos5 = string.Format(" AND A.HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {0}) {1}", hoslevel, sortsource2);

            string orderby = " ORDER BY PurchaseAmount DESC";
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;

            }
            string sql = string.Empty;
            if (vs.Flag == "0")
            {
                sql = string.Format(@"SELECT  Com.*,
                                                ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,
                                                ISNULL(Dis.DistributeAmount, 0) DistributeAmount,
                                                ISNULL(Warehouse.WarehouseAmount, 0) WarehouseAmount,
                                                ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount
                                        FROM    ( SELECT    CompanyID,
                                                            CompanyName
                                                  FROM      TB_Company A
                                                            INNER JOIN dbo.TB_Role_User B ON A.CompanyID = B.UserID
                                                  WHERE     RoleID IN ( '05', '06', '07' )
                                                ) Com
                                                LEFT JOIN ( SELECT  CompanyID_TB,
                                                                    SUM(Price * PurchaseCount) PurchaseAmount
                                                            FROM    dbo.TB_PurchaseOrderDetail A
                                                                    INNER JOIN TB_Goods B ON A.ProcureCatalogID = B.GoodsID
                                                            GROUP BY CompanyID_TB
                                                          ) Pur ON Com.CompanyID = Pur.CompanyID_TB
                                                LEFT JOIN ( SELECT  CompanyID_TB,
                                                                    SUM(Price * A.DistributeCount) DistributeAmount
                                                            FROM    TB_PurchaseOrderDetail A
                                                                    INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                    INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                    INNER JOIN dbo.TB_Goods D ON A.ProcureCatalogID = D.GoodsID
                                                            WHERE   1 = 1
                                                            GROUP BY CompanyID_TB
                                                          ) Dis ON Com.CompanyID = Dis.CompanyID_TB
                                                LEFT JOIN ( SELECT  CompanyID_TB,
                                                                    SUM(Price * A.WarehouseCount) WarehouseAmount
                                                            FROM    TB_PurchaseOrderDetail A
                                                                    INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                    INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                    INNER JOIN dbo.TB_Goods D ON A.ProcureCatalogID = D.GoodsID
                                                            WHERE   1 = 1
                                                            GROUP BY CompanyID_TB
                                                          ) Warehouse ON Com.CompanyID = Warehouse.CompanyID_TB
                                                LEFT JOIN ( SELECT  CompanyID_TB,
                                                                    SUM(RetrunPrice * ReturnCount) ReturnAmount
                                                            FROM    dbo.TB_ReturnMerchandiseDetail A
                                                                    INNER JOIN dbo.TB_Goods B ON A.ProcureCatalogID = B.GoodsID
                                                            GROUP BY CompanyID_TB
                                                          ) ReturnMer ON Com.CompanyID = ReturnMer.CompanyID_TB
                                                ORDER BY PurchaseAmount DESC
                                                ", endTime, startTime, vs.CompanyName_PS, hos + strAreadId, hos5 + strAreadId, orderby);
            }
            else
            {
                sql = string.Format(@"SELECT  Com.*,
                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,
                                                        ISNULL(Dis.DistributeAmount, 0) DistributeAmount,
                                                        ISNULL(Warehouse.WarehouseAmount, 0) WarehouseAmount,
                                                        ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount
                                                FROM    ( SELECT    CompanyID,
                                                                    CompanyName
                                                          FROM      TB_Company A
                                                                    INNER JOIN dbo.TB_Role_User B ON A.CompanyID = B.UserID
                                                          WHERE     RoleID IN ( '05', '06', '07' )
                                                        ) Com
                                                        LEFT JOIN ( SELECT  CompanyID_TB,
                                                                            SUM(Price * PurchaseCount) PurchaseAmount
                                                                    FROM    dbo.TB_PurchaseOrderDetail A
                                                                            INNER JOIN TB_Goods B ON A.ProcureCatalogID = B.GoodsID
                                                                    GROUP BY CompanyID_TB
                                                                  ) Pur ON Com.CompanyID = Pur.CompanyID_TB
                                                        LEFT JOIN ( SELECT  CompanyID_TB,
                                                                            SUM(Price * A.DistributeCount) DistributeAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                            INNER JOIN dbo.TB_Goods D ON A.ProcureCatalogID = D.GoodsID
                                                                    WHERE   1 = 1
                                                                    GROUP BY CompanyID_TB
                                                                  ) Dis ON Com.CompanyID = Dis.CompanyID_TB
                                                        LEFT JOIN ( SELECT  CompanyID_TB,
                                                                            SUM(Price * A.WarehouseCount) WarehouseAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                            INNER JOIN dbo.TB_Goods D ON A.ProcureCatalogID = D.GoodsID
                                                                    WHERE   1 = 1
                                                                    GROUP BY CompanyID_TB
                                                                  ) Warehouse ON Com.CompanyID = Warehouse.CompanyID_TB
                                                        LEFT JOIN ( SELECT  CompanyID_TB,
                                                                            SUM(RetrunPrice * ReturnCount) ReturnAmount
                                                                    FROM    dbo.TB_ReturnMerchandiseDetail A
                                                                            INNER JOIN dbo.TB_Goods B ON A.ProcureCatalogID = B.GoodsID
                                                                    GROUP BY CompanyID_TB
                                                                  ) ReturnMer ON Com.CompanyID = ReturnMer.CompanyID_TB
                                                ORDER BY PurchaseAmount DESC
                                                     ", endTime, startTime, vs.CompanyName_PS, hos + strAreadId, hos5 + strAreadId);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 医院汇总统计（高值）：【采购金额】【响应金额】点击后的详细
        /// JOHN add
        /// </summary>
        /// <param name="vs">Flag=0 【采购金额】 Flag=1【响应金额】</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns>DataTable数据集</returns>
        public static DataTable GetTotalDetail(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            vs.BackPage = "";
            string where = "";
            string strFlag = vs.Flag;
            vs.Flag = "";
            if (strFlag != "3")
            {
                where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
            }

            /*
              Purchase  Distribute  Warehouse  Sell  Return
             */
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.SubmiTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.SubmiTimeTo.ToString();
            }
            string sql = "";


            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            //采购金额
            if (strFlag == "0")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName_PS,HospitalName,SubmiTime,
                                SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE  SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 0 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                         startTime, endTime, where, orderby);
            }
            //采购金额汇总
            else if (strFlag == "0.5")
            {
                sql = string.Format(@"
                                 SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE  SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 0 and  1 = 1 {2} ",
                                         startTime, endTime, where);
            }
            //配送金额
            else if (strFlag == "4")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName_PS,HospitalName,SubmiTime,
                                SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE  SubmiTime between '{0}' and '{1}' and DistributeCount>0  AND OrderDetailState > 0 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                         startTime, endTime, where, orderby);
            }
            //配送金额汇总
            else if (strFlag == "4.5")
            {
                sql = string.Format(@"
                                 SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE  SubmiTime between '{0}' and '{1}' and DistributeCount>0  AND OrderDetailState > 0 and  1 = 1 {2} ",
                                         startTime, endTime, where);
            }
            //入库金额
            else if (strFlag == "5")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName_PS,HospitalName,SubmiTime,
                                SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE  SubmiTime between '{0}' and '{1}' and WarehouseCount>0  AND OrderDetailState > 0 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                         startTime, endTime, where, orderby);
            }
            //入库金额汇总
            else if (strFlag == "5.5")
            {
                sql = string.Format(@"
                                SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE  SubmiTime between '{0}' and '{1}' and WarehouseCount>0  AND OrderDetailState > 0 and  1 = 1 {2} ",
                                         startTime, endTime, where);
            }
            //响应金额
            else if (strFlag == "1")
            {
                sql = string.Format(@"
                                    SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB, CompanyName_PS,HospitalName,SubmiTime,
                                    SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                    SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 1 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                       startTime, endTime, where, orderby);
            }
            //响应金额汇总
            else if (strFlag == "1.5")
            {
                sql = string.Format(@"
                                    SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetail
                                WHERE SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 1 and  1 = 1 {2} ",
                                       startTime, endTime, where);
            }
            //退货金额
            else if (strFlag == "3" || strFlag == "3.5")
            {
                string rstartTime = "2001-10-1";
                string rendTime = "2060-12-1";

                if (!string.IsNullOrEmpty(Convert.ToString(vs.SubmiTimeFrom)))
                {

                    rstartTime = vs.SubmiTimeFrom.ToString();
                    vs.SubmiTimeFrom = null;
                }
                if (!string.IsNullOrEmpty(Convert.ToString(vs.SubmiTimeTo)))
                {
                    rendTime = vs.SubmiTimeTo.ToString();
                    vs.SubmiTimeTo = null;
                }
                string sscom = "", pscom = "", strWhere = "";
                if (!string.IsNullOrEmpty(vs.CompanyID_PS))
                {
                    strWhere = string.Format(" and CompanyID = '{0}' and charindex('{1}',CompanyName)>0  ", vs.CompanyID_PS, vs.CompanyName_PS);
                    sscom = vs.CompanyID_PS;
                    pscom = vs.CompanyName_PS;
                    vs.CompanyID_PS = null;
                    vs.CompanyName_PS = null;
                }
                where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
                //重新加入
                vs.SubmiTimeFrom = Convert.ToDateTime(rstartTime);
                vs.SubmiTimeTo = Convert.ToDateTime(rendTime);
                vs.CompanyID_PS = sscom;
                vs.CompanyName_PS = pscom;
                if (strFlag == "3")
                {
                    sql = string.Format(@"
                                    SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName CompanyName_PS,HospitalName,ReturnTime,
                                    abs(SUM(ReturnAmount)) ReturnAmount,abs(SUM(ReturnCount)) ReturnCount
                                FROM    View_ReturnMerchandiseDetail
                                WHERE ReturnTime between '{0}' and '{1}' AND ReturnState = 1 {4} and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName,HospitalName,ReturnTime  {3}",
                                           rstartTime, rendTime, where, orderby, strWhere);
                }
                else if (strFlag == "3.5")
                {
                    sql = string.Format(@"
                                    SELECT isnull(abs(SUM(ReturnAmount)),0) ReturnAmount,isnull(abs(SUM(ReturnCount)),0) ReturnCount
                                FROM    View_ReturnMerchandiseDetail
                                WHERE ReturnTime between '{0}' and '{1}' AND ReturnState = 1 {4} and  1 = 1 {2}  {3}",
                                           rstartTime, rendTime, where, "", strWhere);
                }

            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 医院汇总统计（低值）：【采购金额】【响应金额】点击后的详细
        /// </summary>
        /// <param name="vs">Flag=0 【采购金额】 Flag=1【响应金额】</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns>DataTable数据集</returns> 
        public static DataTable GetTotalDetail_DZ(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            vs.BackPage = "";
            string where = "";
            string strFlag = vs.Flag;
            vs.Flag = "";
            if (strFlag != "3")
            {
                where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
            }

            /*
              Purchase  Distribute  Warehouse  Sell  Return
             */
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.SubmiTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.SubmiTimeTo.ToString();
            }
            string sql = "";


            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            //采购金额
            if (strFlag == "0")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName_PS,HospitalName,SubmiTime,
                                SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE  SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 0 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                         startTime, endTime, where, orderby);
            }
            //采购金额汇总
            else if (strFlag == "0.5")
            {
                sql = string.Format(@"
                                 SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE  SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 0 and  1 = 1 {2} ",
                                         startTime, endTime, where);
            }
            //配送金额
            else if (strFlag == "4")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName_PS,HospitalName,SubmiTime,
                                SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE  SubmiTime between '{0}' and '{1}' and DistributeCount>0  AND OrderDetailState > 0 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                         startTime, endTime, where, orderby);
            }
            //配送金额汇总
            else if (strFlag == "4.5")
            {
                sql = string.Format(@"
                                 SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE  SubmiTime between '{0}' and '{1}' and DistributeCount>0  AND OrderDetailState > 0 and  1 = 1 {2} ",
                                         startTime, endTime, where);
            }
            //入库金额
            else if (strFlag == "5")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName_PS,HospitalName,SubmiTime,
                                SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE  SubmiTime between '{0}' and '{1}' and WarehouseCount>0  AND OrderDetailState > 0 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                         startTime, endTime, where, orderby);
            }
            //入库金额汇总
            else if (strFlag == "5.5")
            {
                sql = string.Format(@"
                                SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE  SubmiTime between '{0}' and '{1}' and WarehouseCount>0  AND OrderDetailState > 0 and  1 = 1 {2} ",
                                         startTime, endTime, where);
            }
            //响应金额
            else if (strFlag == "1")
            {
                sql = string.Format(@"
                                    SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB, CompanyName_PS,HospitalName,SubmiTime,
                                    SUM(PurchaseCount*Price) PurAmount,SUM(DistributeCount*Price) DisAmount,SUM(WarehouseCount*Price) WareAmount,
                                    SUM(PurchaseCount) PurCount,SUM(DistributeCount) DisCount,SUM(WarehouseCount) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 1 and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName_PS,HospitalName,SubmiTime {3}",
                                       startTime, endTime, where, orderby);
            }
            //响应金额汇总
            else if (strFlag == "1.5")
            {
                sql = string.Format(@"
                                    SELECT  isnull(SUM(PurchaseCount*Price),0) PurAmount,isnull(SUM(DistributeCount*Price),0) DisAmount,isnull(SUM(WarehouseCount*Price),0) WareAmount,
                                    isnull(SUM(PurchaseCount),0) PurCount,isnull(SUM(DistributeCount),0) DisCount,isnull(SUM(WarehouseCount),0) wareCount
                                FROM    View_PurchaseOrderDetailDZ
                                WHERE SubmiTime between '{0}' and '{1}' and PurchaseCount>0  AND OrderDetailState > 1 and  1 = 1 {2} ",
                                       startTime, endTime, where);
            }
            //退货金额
            else if (strFlag == "3" || strFlag == "3.5")
            {
                string rstartTime = "2001-10-1";
                string rendTime = "2060-12-1";

                if (!string.IsNullOrEmpty(Convert.ToString(vs.SubmiTimeFrom)))
                {

                    rstartTime = vs.SubmiTimeFrom.ToString();
                    vs.SubmiTimeFrom = null;
                }
                if (!string.IsNullOrEmpty(Convert.ToString(vs.SubmiTimeTo)))
                {
                    rendTime = vs.SubmiTimeTo.ToString();
                    vs.SubmiTimeTo = null;
                }
                string sscom = "", pscom = "", strWhere = "";
                if (!string.IsNullOrEmpty(vs.CompanyID_PS))
                {
                    strWhere = string.Format(" and CompanyID = '{0}' and charindex('{1}',CompanyName)>0  ", vs.CompanyID_PS, vs.CompanyName_PS);
                    sscom = vs.CompanyID_PS;
                    pscom = vs.CompanyName_PS;
                    vs.CompanyID_PS = null;
                    vs.CompanyName_PS = null;
                }
                where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
                //重新加入
                vs.SubmiTimeFrom = Convert.ToDateTime(rstartTime);
                vs.SubmiTimeTo = Convert.ToDateTime(rendTime);
                vs.CompanyID_PS = sscom;
                vs.CompanyName_PS = pscom;
                if (strFlag == "3")
                {
                    sql = string.Format(@"
                                    SELECT  GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,CompanyName CompanyName_PS,HospitalName,ReturnTime,
                                    abs(SUM(ReturnAmount)) ReturnAmount,abs(SUM(ReturnCount)) ReturnCount
                                FROM    View_ReturnMerchandiseDetailDZ
                                WHERE ReturnTime between '{0}' and '{1}' AND ReturnState = 1 {4} and  1 = 1 {2}
                                GROUP BY GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,companyName_TB,HospitalID,HospitalName,CompanyName,HospitalName,ReturnTime  {3}",
                                           rstartTime, rendTime, where, orderby, strWhere);
                }
                else if (strFlag == "3.5")
                {
                    sql = string.Format(@"
                                    SELECT isnull(abs(SUM(ReturnAmount)),0) ReturnAmount,isnull(abs(SUM(ReturnCount)),0) ReturnCount
                                FROM    View_ReturnMerchandiseDetailDZ
                                WHERE ReturnTime between '{0}' and '{1}' AND ReturnState = 1 {4} and  1 = 1 {2}  {3}",
                                           rstartTime, rendTime, where, "", strWhere);
                }

            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 医院汇总统计：【采购金额】【响应金额】点击后的详细
        /// JOHN add
        /// </summary>
        /// <param name="vs">Flag=0 【采购金额】 Flag=1【响应金额】</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns>DataTable数据集</returns>
        public static DataTable GetTotalDetailByComResponse(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);

            
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
 
            }
            string sql = ""; 
            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            if (vs.Flag == "0")
            {
                sql = string.Format(@"
                                SELECT  GoodsID,
                                        ProductName,
                                        Outlookc,
                                        Unit,
                                        Brand,
                                        Price,
                                        SUM(PurchaseCount) PurchaseCount,
                                        SUM(Price * PurchaseCount) PurchaseAmount,
                                        CompanyName_SC,
                                        companyName_TB,
                                        CompanyName CompanyName_PS,
                                        A.SubmiTime
                                FROM    dbo.TB_PurchaseOrder A
                                        INNER JOIN TB_PurchaseOrderDetail B ON A.OrderID = B.OrderID
                                        INNER JOIN TB_Goods Z ON B.ProcureCatalogID = Z.GoodsID
                                        INNER JOIN dbo.TB_Company X ON B.CompanyID_PS = X.CompanyID
                                WHERE   1 = 1 AND OrderDetailState > 0
                                GROUP BY GoodsID,
                                        ProductName,
                                        Outlookc,
                                        Unit,
                                        Brand,
                                        Price,
                                        CompanyName_SC,
                                        companyName_TB,
                                        CompanyName,
                                        A.SubmiTime", endTime, startTime, where, orderby);
            }
            else
            {
                sql = string.Format(@"
                                    SELECT  GoodsID,
                                        ProductName,
                                        Outlookc,
                                        Unit,
                                        Brand,
                                        Price,
                                        SUM(PurchaseCount) PurchaseCount,
                                        SUM(Price * PurchaseCount) PurchaseAmount,
                                        CompanyName_SC,
                                        companyName_TB,
                                        CompanyName CompanyName_PS,
                                        A.SubmiTime
                                FROM    dbo.TB_PurchaseOrder A
                                        INNER JOIN TB_PurchaseOrderDetail B ON A.OrderID = B.OrderID
                                        INNER JOIN TB_Goods Z ON B.ProcureCatalogID = Z.GoodsID
                                        INNER JOIN dbo.TB_Company X ON B.CompanyID_PS = X.CompanyID
                                WHERE   1 = 1 AND OrderDetailState > 1
                                GROUP BY GoodsID,
                                        ProductName,
                                        Outlookc,
                                        Unit,
                                        Brand,
                                        Price,
                                        CompanyName_SC,
                                        companyName_TB,
                                        CompanyName,
                                        A.SubmiTime
                                        ", endTime, startTime, where);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 配送详情
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetTotalDetailByDistribution(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);
            string sql = "";
            /*
              Purchase  Distribute  Warehouse  Sell  Return
             */
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();

                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}
            }



            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            if (vs.Flag == "0")
            {


                sql = string.Format(@"SELECT ProcureCatalogID,
		                            ProductName,
		                            DistributeCount,
		                            Pack ,Unit,
		                            Price ,
		                            MedicineModelName ,
		                            Factor ,
		                            GovPricing ,
		                            HospitalID,
		                            HospitalName ,
		                            CompanyName_TB ,
		                            CompanyName_PS ,
                                    CompanyName_SC ,
		                            DistributeTime,
		                            DistributionSerialID
                            FROM view_JG WHERE 1=1 AND DistributionSerialID IS NOT NULL
                                        AND DistributeTime <'{0}' AND DistributeTime >='{1}' {2} 
                            GROUP BY ProcureCatalogID,
		                            ProductName,
		                            DistributeCount,
		                            Pack ,Unit,
		                            Price ,
		                            MedicineModelName ,
		                            Factor ,
		                            GovPricing ,
		                            HospitalID,
		                            HospitalName ,
		                            CompanyName_TB ,
		                            CompanyName_PS ,
                                    CompanyName_SC ,
		                            DistributeTime,
		                            DistributionSerialID {3}", endTime, startTime, where, orderby);
            }
            else
            {
                sql = string.Format(@"SELECT  ISNULL(SUM(DistributeCount * Price), 0) DisAmount ,
                                        ISNULL(COUNT(*), 0) DisCount
                                    FROM    (SELECT ProcureCatalogID,
		                            ProductName,
		                            DistributeCount,
		                            Pack ,Unit,
		                            Price ,
		                            MedicineModelName ,
		                            Factor ,
		                            GovPricing ,
		                            HospitalID,
		                            HospitalName ,
		                            CompanyName_TB ,
		                            CompanyName_PS ,
                                    CompanyName_SC ,
		                            DistributeTime,
		                            DistributionSerialID
                            FROM view_JG WHERE 1=1 AND DistributionSerialID IS NOT NULL
                                        AND DistributeTime <'{0}' AND DistributeTime >='{1}' {2} 
                            GROUP BY ProcureCatalogID,
		                            ProductName,
		                            DistributeCount,
		                            Pack ,Unit,
		                            Price ,
		                            MedicineModelName ,
		                            Factor ,
		                            GovPricing ,
		                            HospitalID,
		                            HospitalName ,
		                            CompanyName_TB ,
		                            CompanyName_PS ,
                                    CompanyName_SC ,
		                            DistributeTime,
		                            DistributionSerialID 
                                            ) AA 
                                        ", endTime, startTime, where);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 汇总详情
        /// </summary>
        /// <param name="vs"></param>
        /// <returns></returns>
        public static string TotalWhere(SuperviseQuery vs)
        {
            string where = "";




            if (!string.IsNullOrEmpty(vs.ProcureCatalogID))
            {
                where += string.Format(" AND  ProcureCatalogID='{0}'", vs.ProcureCatalogID);
            }
            if (!string.IsNullOrEmpty(vs.HospitalID))
            {
                where += string.Format(" AND  HospitalID='{0}'", vs.HospitalID);
            }

            if (!string.IsNullOrEmpty(vs.HospitalName))
            {
                where += string.Format(" AND HospitalName LIKE '%{0}%'", vs.HospitalName);
            }
            if (!string.IsNullOrEmpty(vs.ProductName))
            {
                where += string.Format(" AND ProductName LIKE '%{0}%'", vs.ProductName);

            }
            if (!string.IsNullOrEmpty(vs.PinyinCode))
            {
                where += string.Format(" AND PinyinCode LIKE '%{0}%'", vs.PinyinCode);

            }
            //if (!string.IsNullOrEmpty(vs.ProductName))
            //{
            //    if (HZ.Common.zhHansUtil.HaveChineseCode(vs.ProductName))
            //    {
            //        where += string.Format(" AND ProductName LIKE '%{0}%'", vs.ProductName);
            //    }
            //    else
            //    {
            //        where += string.Format(" AND PinyinCode LIKE '%{0}%'", vs.ProductName);

            //    }
            //}
            if (!string.IsNullOrEmpty(vs.Pack))
            {
                where += string.Format(" AND Pack LIKE '%{0}%'", vs.Pack);

            }
            if (!string.IsNullOrEmpty(vs.MedicineModelName))
            {
                where += string.Format(" AND MedicineModelName LIKE '%{0}%'", vs.MedicineModelName);
            }
            if (!string.IsNullOrEmpty(vs.CompanyName_TB))
            {
                where += string.Format(" AND CompanyName_TB LIKE '%{0}%'", vs.CompanyName_TB);

            }
            if (!string.IsNullOrEmpty(vs.CompanyName_SC))
            {
                where += string.Format(" AND CompanyName_SC LIKE '%{0}%'", vs.CompanyName_SC);

            }
            if (!string.IsNullOrEmpty(vs.CompanyName_PS))
            {
                where += string.Format("AND CompanyName_PS LIKE '%{0}%'", vs.CompanyName_PS);

            }
            //AND CompanyID_PS ='' AND CompanyID_TB=''
            if (!string.IsNullOrEmpty(vs.CompanyID_PS))
            {
                where += string.Format("AND CompanyID_PS ='{0}'", vs.CompanyID_PS);

            }
            if (!string.IsNullOrEmpty(vs.CompanyID_SC))
            {
                where += string.Format("AND CompanyID_SC ='{0}'", vs.CompanyID_SC);

            }

            if (!string.IsNullOrEmpty(vs.CompanyID_TB))
            {
                where += string.Format("AND CompanyID_TB  ='{0}'", vs.CompanyID_TB);
            }

            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                where += string.Format(" AND IsGovernment ='{0}'", vs.IsGovernment);
            }
            if (!string.IsNullOrEmpty(vs.IsEssential))
            {
                where += string.Format(" AND IsEssential ='{0}'", vs.IsEssential);
            }
            //AND OrderName LIKE '%%' AND BatchRecordID ='' AND InvoiceID ='' 

            if (!string.IsNullOrEmpty(vs.OrderName))
            {
                where += string.Format(" AND OrderName LIKE '%{0}%' ", vs.OrderName);
            }
            if (!string.IsNullOrEmpty(vs.BatchRecordID))
            {
                where += string.Format("  AND BatchRecordID ='{0}'", vs.BatchRecordID);
            }
            if (!string.IsNullOrEmpty(vs.InvoiceID))
            {
                where += string.Format("  AND InvoiceID ='{0}'", vs.InvoiceID);
            }

            if (!string.IsNullOrEmpty(vs.AreaID))
            {
                //where += vs.AreaID;
                where += string.Format(" AND AreaId  like '%{0}%'", vs.AreaID);
                //string areaid = "";
                //if (vs.AreaID == "510000")
                //{
                //    areaid = "22";
                //}
                //else
                //{
                //    string mm = vs.AreaID.Substring(4, 2);
                //    if (vs.AreaID.Substring(4, 2) == "00")
                //    {
                //        areaid = vs.AreaID.Substring(0, 4);
                //    }
                //    else
                //    {
                //        areaid = vs.AreaID;
                //    }
                //}
                //where += string.Format("AND AreaId  like '{0}%'", areaid);

            }
            if (!string.IsNullOrEmpty(vs.IsCountry))
            {

                where += string.Format(" AND IsCountryDetail IN({0})", vs.IsCountry);

            }
            if (!string.IsNullOrEmpty(vs.Levels))
            {

                where += string.Format(" AND Levels IN ({0})", vs.Levels);

            }
            return where;

        }

        /// <summary>
        /// 入库详情
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetTotalDetailByWarehouse(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);
            string sql = "";
            /*
              Purchase  Distribute  Warehouse  Sell  Return
             */
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();

                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}
            }



            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            if (vs.Flag == "0")
            {

                //                sql = string.Format(@"SELECT  ProcureCatalogID ,
                //                                            ProductName ,
                //                                            WarehouseCount , 
                //                                            Pack ,unit,
                //                                            ProcurePrice ,
                //                                            MedicineModelName ,
                //                                            Factor ,
                //                                            QualityLevel ,
                //                                            GovPricing ,
                //                                            SortName ,
                //                                            HospitalName ,
                //                                            CompanyName_TB ,
                //                                            CompanyName_PS ,
                //                                            WarehouseTime
                //                                    FROM    VIEW_Distribution
                //                                    WHERE   1=1 AND DistributeState > 0 AND WarehouseTime <'{0}' AND WarehouseTime >='{1}' {2}
                //                                    {3}", endTime, startTime, where, orderby);
                sql = string.Format(@"
									SELECT  ProcureCatalogID ,
                                            ProductName ,
                                            WarehouseCount , 
                                            Pack ,unit,
                                            Price ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,
                                            CompanyName_SC ,
                                            DistributionSerialID,
                                            WarehouseTime,
                                            BatchRecordID,
                                            InvoiceID,
                                            OrderName
                                    FROM    View_JG
                                    WHERE 1=1 AND  DistributionSerialID IS NOT NULL AND DistributeState > 0 AND WarehouseTime <'{0}' AND WarehouseTime >='{1}' {2}
                                    GROUP BY ProcureCatalogID ,
                                            ProductName ,
                                            WarehouseCount , 
                                            Pack ,unit,
                                            Price ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,
                                            CompanyName_SC ,
                                            DistributionSerialID,
                                            WarehouseTime,
                                            BatchRecordID,
                                            InvoiceID,
                                            OrderName {3}", endTime, startTime, where, orderby);
            }
            else
            {
                sql = string.Format(@"SELECT  ISNULL(SUM(WarehouseCount * Price), 0) WareAmount ,
                                        ISNULL(COUNT(*), 0) wareCount
                                    FROM    ( SELECT    ProcureCatalogID ,
                                                        ProductName ,
                                                        WarehouseCount ,
                                                        Pack ,
                                                        unit ,
                                                        Price ,
                                                        MedicineModelName ,
                                                        Factor ,
                                                        GovPricing ,
                                                        HospitalName ,
                                                        CompanyName_TB ,
                                                        CompanyName_PS ,
                                                        CompanyName_SC ,
                                                        DistributionSerialID ,
                                                        WarehouseTime ,
                                                        BatchRecordID ,
                                                        InvoiceID ,
                                                        OrderName
                                              FROM      View_JG
                                              WHERE     1 = 1
                                                        AND DistributionSerialID IS NOT NULL
                                                        AND DistributeState > 0
                                                        AND WarehouseTime < '{0}'
                                                        AND WarehouseTime >= '{1}' {2}
                                              GROUP BY  ProcureCatalogID ,
                                                        ProductName ,
                                                        WarehouseCount ,
                                                        Pack ,
                                                        unit ,
                                                        Price ,
                                                        MedicineModelName ,
                                                        Factor ,
                                                        GovPricing ,
                                                        HospitalName ,
                                                        CompanyName_TB ,
                                                        CompanyName_PS ,
                                                        CompanyName_SC ,
                                                        DistributionSerialID ,
                                                        WarehouseTime ,
                                                        BatchRecordID ,
                                                        InvoiceID ,
                                                        OrderName
                                            ) AA 
                                        ", endTime, startTime, where);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }
        /// <summary>
        /// 销售详情
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetTotalDetailBySell(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);
            string sql = "";
            /*
              Purchase  Distribute  Warehouse  Sell  Return
             */
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}
            }



            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            if (vs.Flag == "0")
            {

                sql = string.Format(@"
									SELECT ProcureCatalogID ,
                                            ProductName ,
                                            SellCount ,
                                            Pack ,Unit, 
                                            MedicineModelName ,
                                            Factor ,
                                            SellPrice ,Price,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,
                                            CompanyName_SC ,
                                            SellDetailID,
                                            SellTime
                                     FROM   View_JG
                                     WHERE 1=1 AND SellDetailID IS NOT NULL
                                     AND SellTime <'{0}' AND SellTime >='{1}' {2}
                                     GROUP BY ProcureCatalogID ,
                                            ProductName ,
                                            SellCount ,
                                            Pack ,Unit, 
                                            MedicineModelName ,
                                            Factor ,
                                            SellPrice ,Price,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,
                                            CompanyName_SC ,
                                            SellDetailID,
                                            SellTime {3}", endTime, startTime, where, orderby);
            }
            else
            {
                sql = string.Format(@"SELECT  ISNULL(SUM(SellCount * SellPrice), 0) SellAmount ,
                                        ISNULL(COUNT(*), 0) SellCount
                                    FROM    ( SELECT ProcureCatalogID ,
                                            ProductName ,
                                            SellCount ,
                                            Pack ,Unit,
                                            SellPrice ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,
                                            CompanyName_SC ,
                                            SellDetailID,
                                            SellTime
                                     FROM   View_JG
                                     WHERE 1=1 AND SellDetailID IS NOT NULL
                                     AND SellTime <'{0}' AND SellTime >='{1}' {2}
                                     GROUP BY ProcureCatalogID ,
                                            ProductName ,
                                            SellCount ,
                                            Pack ,Unit,
                                            SellPrice ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,
                                            CompanyName_SC ,
                                            SellDetailID,
                                            SellTime 
                                            ) AA 
                                        ", endTime, startTime, where);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 退货详情
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetTotalDetailByReturn(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);
            string sql = "";
            /*
              Purchase  Distribute  Warehouse  Sell  Return
             */
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();

                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}
            }



            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            if (vs.Flag == "0")
            {
                sql = string.Format(@"SELECT ProcureCatalogID ,
                                            ProductName ,
                                            ReturnCount ,
                                            Pack ,Unit,
                                            Price ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,CompanyName_SC ,
                                            ReturnSerialDetailID,
                                            OverTime
                                     FROM   View_JG 
                                     WHERE 1=1 AND ReturnSerialDetailID IS NOT NULL
                                     AND ReturnState = 3  AND OverTime <'{0}' AND OverTime >='{1}' {2}
                                     GROUP BY ProcureCatalogID ,
                                            ProductName ,
                                            ReturnCount ,
                                            Pack ,Unit,
                                            Price ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,CompanyName_SC ,
                                            ReturnSerialDetailID,
                                            OverTime {3}", endTime, startTime, where, orderby);
            }
            else
            {
                sql = string.Format(@"SELECT  ISNULL(SUM(ReturnCount * Price), 0) ReturnAmount ,
                                        ISNULL(COUNT(*), 0) ReturnCount
                                    FROM    ( SELECT ProcureCatalogID ,
                                            ProductName ,
                                            ReturnCount ,
                                            Pack ,Unit,
                                            Price ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,CompanyName_SC ,
                                            ReturnSerialDetailID,
                                            OverTime
                                     FROM   View_JG 
                                     WHERE 1=1 AND ReturnSerialDetailID IS NOT NULL
                                     AND ReturnState = 3  AND OverTime <'{0}' AND OverTime >='{1}' {2}
                                     GROUP BY ProcureCatalogID ,
                                            ProductName ,
                                            ReturnCount ,
                                            Pack ,Unit,
                                            Price ,
                                            MedicineModelName ,
                                            Factor ,
                                            GovPricing ,
                                            HospitalName ,
                                            CompanyName_TB ,
                                            CompanyName_PS ,CompanyName_SC ,
                                            ReturnSerialDetailID,
                                            OverTime  
                                            ) AA 
                                        ", endTime, startTime, where);
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 库存详情
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetTotalDetailByStock(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);
            string sql = "";
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            //if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            //{ 
            //    startTime = vs.CenSubmiitTimeFrom.ToString();
            //}
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
            }
            string orderby = string.Empty;
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = " ORDER BY " + vs.SortFields;
            }
            if (vs.Flag == "0")
            {
                sql = string.Format(@"SELECT  *
                                        FROM    ( SELECT    pro.* ,
                                                            ware.HospitalID ,ware.HospitalName,
                                                            ISNULL(WarehouseCount, 0) - ISNULL(SellCount, 0)
                                                            - ISNULL(ReturnCount, 0) StockCount
                                                  FROM      ( SELECT    *
                                                              FROM      dbo.View_ProcureCatalog_TB
                                                            ) pro
                                                            INNER JOIN ( SELECT HospitalID ,HospitalName,
                                                                                ProcureCatalogID ,
                                                                                SUM(WarehouseCount) WarehouseCount
                                                                         FROM   dbo.TB_Distribution
                                                                         WHERE  DistributeState > 0
                                                                                AND WarehouseTime >= '{0}'
                                                                                AND WarehouseTime < '{1}'
                                                                         GROUP BY HospitalID ,HospitalName,
                                                                                ProcureCatalogID
                                                                       ) ware ON pro.ProcureCatalogID = ware.ProcureCatalogID
                                                            LEFT JOIN ( SELECT  HospitalID ,
                                                                                ProcureCatalogID ,
                                                                                SUM(SellCount) SellCount
                                                                        FROM    dbo.TB_HospitalSellDetail
                                                                        WHERE   SellTime >= '{0}'
                                                                                AND SellTime < '{1}'
                                                                        GROUP BY HospitalID ,
                                                                                ProcureCatalogID
                                                                      ) sell ON ware.HospitalID = sell.HospitalID
                                                                                AND ware.ProcureCatalogID = sell.ProcureCatalogID
                                                            LEFT JOIN ( SELECT  HospitalID ,
                                                                                ProcureCatalogID ,
                                                                                SUM(ReturnCount) ReturnCount
                                                                        FROM    dbo.TB_ReturnMerchandiseDetail
                                                                        WHERE   ReturnState = 3
                                                                                AND OverTime >=  '{0}'
                                                                                AND OverTime < '{1}'
                                                                        GROUP BY HospitalID ,
                                                                                ProcureCatalogID
                                                                      ) ret ON ware.HospitalID = ret.HospitalID
                                                                               AND ware.ProcureCatalogID = ret.ProcureCatalogID
                                                ) AA WHERE   1 = 1  {2} {3}", startTime, endTime, where, orderby);
            }
            else
            {

            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }
        /// <summary>
        /// 药政处报表
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable ReportByYaoZheng(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string where = TotalWhere(vs);
            string orderby = "";
            if (!string.IsNullOrEmpty(vs.SortFields))
            {
                orderby = vs.SortFields;
            }
            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
                //DateTime d = new DateTime();
                //if (DateTime.TryParse(Convert.ToString(vs.CenSubmiitTimeTo), out d))
                //{
                //    endTime = d.AddDays(1).ToString();
                //}
            }


            string hosInfo = string.Empty;
            string hosbInfo = string.Empty;
            string proInfo = string.Empty;

            if (!string.IsNullOrEmpty(vs.IsCountry))
            {
                hosInfo += string.Format(" AND  IsCountryDetail IN ({0})", vs.IsCountry);
                hosbInfo += string.Format(" AND  b.IsCountryDetail IN ({0})", vs.IsCountry);
            }
            if (!string.IsNullOrEmpty(vs.Levels))
            {
                hosInfo += string.Format(" AND  Levels IN ({0})", vs.Levels);
                hosbInfo += string.Format(" AND  b.Levels IN ({0})", vs.Levels);
            }
            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                hosInfo += string.Format(" AND  IsGovernment IN ({0})", vs.IsGovernment);
                hosbInfo += string.Format(" AND  b.IsGovernment IN ({0})", vs.IsGovernment);

            }
            if (!string.IsNullOrEmpty(vs.AreaID))
            {
                hosInfo += vs.AreaID;

            }
            if (!string.IsNullOrEmpty(vs.IsEssential))
            {
                proInfo += string.Format(" AND IsEssential = '{0}'", vs.IsEssential);
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.SortSource)))
            {
                if (vs.SortSource == 0 || vs.SortSource == 1)
                {
                    proInfo += string.Format("  AND SortSource IN ({0})", "'1','2','3'");

                }
                else
                {
                    proInfo += string.Format("  AND SortSource IN ({0})", "'4'");

                }
            }
            string sql = string.Format(@"
                                        SELECT  *
                                        FROM    ( SELECT    Father.AreaName [市、州] ,
                                                            Father.AreaID faAreaID ,
                                                            area.AreaID AreaID ,
                                                            Father.FatherHos [医疗机构数量] ,
                                                            CASE WHEN area.AreaID IN ( '220100', '220102', '220103',
                                                                                       '220104', '220105', '220106',
                                                                                       '220184', '220185', '220186',
                                                                                       '220187', '220188', '220200',
                                                                                       '220202', '220203', '220204',
                                                                                       '220211', '220285', '220300',
                                                                                       '220302', '220303', '220324',
                                                                                       '220400', '220402', '220403',
                                                                                       '220500', '220502', '220503',
                                                                                       '220600', '220682', '220700',
                                                                                       '220702', '220797', '220798',
                                                                                       '220799', '220800', '220802',
                                                                                       '222400', '222500', '222501',
                                                                                       '222502', '222503', '222599' )
                                                                 THEN '本级'
                                                                 ELSE '县区'
                                                            END [是否本级] ,
                                                            area.AreaName [区、县] ,
                                                            ISNULL(area.AreaHos, 0) [基层医疗机构数量] ,
                                                            ISNULL(purHosCount, 0) [网上采购数量] ,
                                                            ISNULL(area.AreaHos, 0) - ISNULL(purHosCount, 0) [未网上采购数量] ,
                                                            CONVERT(VARCHAR, CONVERT(DECIMAL(15, 2), ISNULL(( CONVERT(FLOAT, purHosCount)
                                                                                                      / area.AreaHos ),
                                                                                                      0) * 100)) + '%' [网上采购机构所占比例] ,
                                                            ISNULL(purAmount, 0) [采购金额] ,
                                                            ISNULL(wareAmount, 0) [入库金额] ,
                                                            ISNULL(sellAmount, 0) [销售金额] ,
                                                            ISNULL(retAmount, 0) [退货金额],
                                                            ISNULL(wareAmount, 0)-ISNULL(retAmount, 0)[应缴款金额]
                                                  FROM      ( SELECT    COUNT(*) FatherHos ,
                                                                        a.AreaID ,
                                                                        AreaName
                                                              FROM      dbo.TB_Area a
                                                                        LEFT JOIN dbo.TB_Hospital b ON b.AreaID LIKE LEFT(a.AreaID,
                                                                                                      4) + '%'
                                                              WHERE     FatherAreaID = '510000'
                                                                         {4}
                                                              GROUP BY  a.AreaID ,
                                                                        AreaName
                                                            ) Father
                                                            LEFT JOIN ( SELECT  a.AreaID , 
                                                                                a.AreaName ,
                                                                                COUNT(*) AreaHos
                                                                        FROM    dbo.TB_Area a
                                                                                INNER JOIN dbo.TB_Hospital b ON a.AreaID = b.AreaID
                                                                        WHERE   1=1 {4}
                                                                        GROUP BY a.AreaID ,
                                                                                a.AreaName
                                                                      ) area ON area.AreaID LIKE LEFT(Father.AreaID, 4)
                                                                                + '%'
                                                            LEFT JOIN ( SELECT  COUNT(DISTINCT HospitalID) purHosCount ,
                                                                                AreaID ,
                                                                                AreaID aa ,
                                                                                AreaName
                                                                        FROM    dbo.TB_PurchaseOrderDetail
                                                                        WHERE   OrderDetailState > 0
                                                                                AND HospitalID IN ( SELECT
                                                                                                      HospitalID
                                                                                                    FROM
                                                                                                      dbo.TB_Hospital
                                                                                                    WHERE
                                                                                                      1=1 {2})
																				AND CenSubmiitTime >= '{0}'
                                                                                AND CenSubmiitTime < '{1}'
                                                                                AND ProcureCatalogID IN (
                                                                                            SELECT  ProcureCatalogID
                                                                                            FROM    dbo.View_ProcureCatalog_TB
                                                                                            WHERE   1=1 {3})
                                                                        GROUP BY AreaID ,
                                                                                AreaName
                                                                      ) purhos ON area.AreaID = purhos.AreaID
                                                            LEFT JOIN ( SELECT  SUM(Amount) purAmount ,
                                                                                AreaID ,
                                                                                AreaName
                                                                        FROM    dbo.TB_PurchaseOrderDetail
                                                                        WHERE   OrderDetailState > 0
                                                                                AND HospitalID IN ( SELECT
                                                                                                      HospitalID
                                                                                                    FROM
                                                                                                      dbo.TB_Hospital
                                                                                                    WHERE
                                                                                                      1=1 {2})
                                                                                AND CenSubmiitTime >= '{0}'
                                                                                AND CenSubmiitTime < '{1}'
                                                                                AND ProcureCatalogID IN (
                                                                                            SELECT  ProcureCatalogID
                                                                                            FROM    dbo.View_ProcureCatalog_TB
                                                                                            WHERE   1=1 {3})
                                                                        GROUP BY AreaID ,
                                                                                AreaName
                                                                      ) pur ON area.AreaID = pur.AreaID
                                                            LEFT JOIN ( SELECT  SUM(WarehouseCount * Price) wareAmount ,
                                                                                AreaID
                                                                        FROM    dbo.TB_Distribution
                                                                        WHERE   DistributeState > 0
                                                                                AND HospitalID IN ( SELECT
                                                                                                      HospitalID
                                                                                                    FROM
                                                                                                      dbo.TB_Hospital
                                                                                                    WHERE
                                                                                                      1=1 {2})
                                                                                AND WarehouseTime >= '{0}'
                                                                                AND WarehouseTime < '{1}'
                                                                                AND ProcureCatalogID IN (
                                                                                            SELECT  ProcureCatalogID
                                                                                            FROM    dbo.View_ProcureCatalog_TB
                                                                                            WHERE   1=1 {3})
                                                                        GROUP BY AreaID
                                                                      ) ware ON area.AreaID = ware.AreaID
                                                            LEFT JOIN ( SELECT  SUM(SellCount * Price) sellAmount ,
                                                                                b.AreaID
                                                                        FROM    dbo.TB_HospitalSellDetail a
                                                                                LEFT JOIN dbo.TB_Hospital b ON a.HospitalID = b.HospitalID
                                                                        WHERE   b.HospitalID IN ( SELECT
                                                                                                      HospitalID
                                                                                                  FROM
                                                                                                      dbo.TB_Hospital
                                                                                                  WHERE
                                                                                                     1=1 {2})
                                                                                AND SellTime >= '{0}'
                                                                                AND SellTime < '{1}'
                                                                                AND ProcureCatalogID IN (
                                                                                            SELECT  ProcureCatalogID
                                                                                            FROM    dbo.View_ProcureCatalog_TB
                                                                                            WHERE   1=1 {3})
                                                                        GROUP BY b.AreaID
                                                                      ) sell ON area.AreaID = sell.AreaID
                                                            LEFT JOIN ( SELECT  SUM(ReturnCount * RetrunPrice) retAmount ,
                                                                                b.AreaID
                                                                        FROM    dbo.TB_ReturnMerchandiseDetail a
                                                                                LEFT JOIN dbo.TB_Hospital b ON a.HospitalID = b.HospitalID
                                                                        WHERE   b.HospitalID IN ( SELECT
                                                                                                      HospitalID
                                                                                                  FROM
                                                                                                      dbo.TB_Hospital
                                                                                                  WHERE
                                                                                                     1=1 {2})
                                                                                AND OverTime >= '{0}'
                                                                                AND OverTime < '{1}' 
                                                                                AND ProcureCatalogID IN (
                                                                                            SELECT  ProcureCatalogID
                                                                                            FROM    dbo.View_ProcureCatalog_TB
                                                                                            WHERE   1=1 {3})
                                                                                AND ReturnState=3
                                                                        GROUP BY b.AreaID
                                                                      ) ret ON area.AreaID = ret.AreaID
                                                ) AA
                                        ORDER BY faAreaID ,[是否本级],
                                                AA.AreaID   ", startTime, endTime, hosInfo, proInfo, hosbInfo);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 没有网上采购的医疗机构
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable PurchaseNotNetByHospital(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            //string sql = "SELECT* FROM dbo.TB_Area WHERE AreaName='{0}'";
            //DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format (sql,vs.AreaName));
            //string tempareaid = "510000";
            //foreach (DataRow dr in dt.Rows)
            //{
            //    tempareaid = dr["AreaID"].ToString();
            //}
            //string areaid = "";
            //if (tempareaid == "510000")
            //{
            //    areaid = "22%";
            //}
            //else
            //{
            //    if (tempareaid.Substring(4, 2) == "00")
            //    {
            //        areaid = tempareaid.Substring(0, 4)+"%";
            //    }
            //    else
            //    {
            //        areaid = tempareaid;
            //    }
            //}


            string startTime = "2001-10-1";
            string endTime = "2060-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();
            }
            string hosInfo = string.Empty;
            string proInfo = string.Empty;

            if (!string.IsNullOrEmpty(vs.IsCountry))
            {
                hosInfo += string.Format(" AND  IsCountryDetail IN ({0})", vs.IsCountry);
            }
            if (!string.IsNullOrEmpty(vs.Levels))
            {
                hosInfo += string.Format(" AND  Levels IN ({0})", vs.Levels);
            }
            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                hosInfo += string.Format(" AND  IsGovernment IN ({0})", vs.IsGovernment);

            }
            if (!string.IsNullOrEmpty(vs.AreaID))
            {
                hosInfo += vs.AreaID;
            }
            if (!string.IsNullOrEmpty(vs.HospitalName))
            {
                hosInfo += string.Format(" AND  HospitalName LIKE '%{0}%'", vs.HospitalName);

            }
            if (!string.IsNullOrEmpty(vs.IsEssential))
            {
                proInfo += string.Format(" AND IsEssential = '{0}'", vs.IsEssential);
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.SortSource)))
            {
                if (vs.SortSource == 0)
                {
                    proInfo += string.Format("  AND SortSource IN ({0})", "'1','2','3'");

                }
                else
                {
                    proInfo += string.Format("  AND SortSource IN ({0})", "'4'");

                }
            }

            string sql = string.Format(@"SELECT  hos.*,area.AreaName
                                FROM    ( SELECT    *
                                          FROM      dbo.TB_Hospital
                                          WHERE     1=1 {0}
                                                    AND HospitalID NOT IN (
                                                    SELECT  HospitalID
                                                    FROM    dbo.TB_PurchaseOrderDetail
                                                    WHERE   OrderDetailState > 0 
                                                            AND HospitalID IN (
                                                            SELECT  HospitalID
                                                            FROM    dbo.TB_Hospital
                                                            WHERE  1=1 {0})
                                                            AND ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB WHERE 1=1 {3})
                                                            AND CenSubmiitTime >= '{1}'
                                                            AND CenSubmiitTime < '{2}' )
                                        ) hos
                                        LEFT JOIN ( SELECT  *
                                                    FROM    dbo.TB_Area
                                                  ) area ON hos.AreaID = area.AreaID ORDER BY HospitalID ASC ", hosInfo, startTime, endTime, proInfo);


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 县区实时汇总
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable AreaStatisticsTotal(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = "";


            string startTime = "2001-10-1";
            string endTime = "2060-12-1";
            string iscountry = "";

            if (!string.IsNullOrEmpty(vs.IsCountry))
            {
                iscountry = string.Format(" AND IsCountryDetail IN({0})", vs.IsCountry);
                vs.IsCountry = "";
            }
            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                iscountry += string.Format(" AND IsGovernment ='{0}' ", vs.IsGovernment);
                vs.IsGovernment = "";
            }
            if (!string.IsNullOrEmpty(vs.Levels))
            {
                iscountry += string.Format(" AND Levels in({0}) ", vs.Levels);
                vs.Levels = "";
            }
            string isess = "";

            if (!string.IsNullOrEmpty(vs.IsEssential))
            {
                isess = string.Format(" AND IsEssential ='{0}'", vs.IsEssential);
                vs.IsEssential = "";
            }
            if (vs.SortSource == 1)
            {
                isess += string.Format(" AND SortSource IN ({0})", "'1','2','3'");
                vs.SortSource = null;
            }
            else if (vs.SortSource == 4)
            {
                isess += string.Format("   AND SortSource IN ({0}) ", "'4'");
                vs.SortSource = null;
            }


            string where = TotalWhere(vs);
            string hos1 = string.Format("AND HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {0}) AND ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB WHERE 1=1 {1})", iscountry, isess);
            string hos2 = string.Format("AND a.HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {0}) AND a.ProcureCatalogID IN (SELECT ProcureCatalogID FROM dbo.View_ProcureCatalog_TB WHERE 1=1 {1})", iscountry, isess);

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString();

            }
            if (vs.Flag == "0")
            {
                sql = string.Format(@"SELECT  area.AreaID ,
                                                        AreaName ,
                                                        SUM(ISNULL(PurchaseAmount, 0)) PurchaseAmount ,
                                                        SUM(ISNULL(DistributeAmount, 0)) DistributeAmount ,
                                                        SUM(ISNULL(WarehouseAmount, 0)) WarehouseAmount ,
                                                        SUM(ISNULL(ReturnAmount, 0)) ReturnAmount ,
                                                        SUM(ISNULL(WarehouseAmount, 0) - ISNULL(ReturnAmount, 0)) payAmount
                                                FROM    ( SELECT    *
                                                          FROM      dbo.TB_Area
                                                          WHERE     1 = 1
                                                                    AND FatherAreaID = '510000'
                                                        ) area
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(Price * PurchaseCount) PurchaseAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN TB_Hospital C ON B.HospitalID = C.HospitalID
                                                                    GROUP BY AreaID
                                                                  ) pur ON area.AreaID = pur.AreaID
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(Price * A.DistributeCount) DistributeAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                            INNER JOIN dbo.TB_Hospital D ON B.HospitalID = D.HospitalID
                                                                    WHERE   1 = 1
                                                                    GROUP BY AreaID
                                                                  ) dis ON pur.AreaID = dis.AreaID
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(Price * A.WarehouseCount) WarehouseAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                            INNER JOIN dbo.TB_Hospital D ON B.HospitalID = D.HospitalID
                                                                    WHERE   1 = 1
                                                                            AND DistributeState > 0
                                                                    GROUP BY AreaID
                                                                  ) ware ON area.AreaID = ware.AreaID
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(RetrunPrice * ReturnCount) ReturnAmount
                                                                    FROM    dbo.TB_ReturnMerchandise A
                                                                            INNER JOIN TB_ReturnMerchandiseDetail B ON A.ReturnSerialID = B.ReturnSerialID
                                                                            INNER JOIN dbo.TB_Hospital C ON A.HospitalID = C.HospitalID
                                                                    GROUP BY AreaID
                                                                  ) ReturnMer ON area.AreaID = ReturnMer.AreaID
                                                GROUP BY AreaName ,
                                                        area.AreaID
                                                ORDER BY PurchaseAmount DESC 
", startTime, endTime, where, vs.SortFields, vs.AreaID, hos1, hos2);
            }
            else
            {
                sql = string.Format(@"SELECT  area.AreaID ,
                                                        AreaName ,
                                                        SUM(ISNULL(PurchaseAmount, 0)) PurchaseAmount ,
                                                        SUM(ISNULL(DistributeAmount, 0)) DistributeAmount ,
                                                        SUM(ISNULL(WarehouseAmount, 0)) WarehouseAmount ,
                                                        SUM(ISNULL(ReturnAmount, 0)) ReturnAmount ,
                                                        SUM(ISNULL(WarehouseAmount, 0) - ISNULL(ReturnAmount, 0)) payAmount
                                                FROM    ( SELECT    *
                                                          FROM      dbo.TB_Area
                                                          WHERE     1 = 1
                                                                    AND FatherAreaID = '510000'
                                                        ) area
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(Price * PurchaseCount) PurchaseAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN TB_Hospital C ON B.HospitalID = C.HospitalID
                                                                    GROUP BY AreaID
                                                                  ) pur ON area.AreaID = pur.AreaID
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(Price * A.DistributeCount) DistributeAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                            INNER JOIN dbo.TB_Hospital D ON B.HospitalID = D.HospitalID
                                                                    WHERE   1 = 1
                                                                    GROUP BY AreaID
                                                                  ) dis ON pur.AreaID = dis.AreaID
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(Price * A.WarehouseCount) WarehouseAmount
                                                                    FROM    TB_PurchaseOrderDetail A
                                                                            INNER JOIN dbo.TB_PurchaseOrder B ON A.OrderID = B.OrderID
                                                                            INNER JOIN dbo.TB_Distribution C ON C.OrderDetailID = A.OrderDetailID
                                                                            INNER JOIN dbo.TB_Hospital D ON B.HospitalID = D.HospitalID
                                                                    WHERE   1 = 1
                                                                            AND DistributeState > 0
                                                                    GROUP BY AreaID
                                                                  ) ware ON area.AreaID = ware.AreaID
                                                        LEFT JOIN ( SELECT  AreaID ,
                                                                            SUM(RetrunPrice * ReturnCount) ReturnAmount
                                                                    FROM    dbo.TB_ReturnMerchandise A
                                                                            INNER JOIN TB_ReturnMerchandiseDetail B ON A.ReturnSerialID = B.ReturnSerialID
                                                                            INNER JOIN dbo.TB_Hospital C ON A.HospitalID = C.HospitalID
                                                                    GROUP BY AreaID
                                                                  ) ReturnMer ON area.AreaID = ReturnMer.AreaID
                                                GROUP BY AreaName ,
                                                        area.AreaID
                                                ORDER BY PurchaseAmount DESC  
", startTime, endTime, where, vs.AreaID, hos2, hos1);
            }

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
    }
}
